<?php
/**
 * Created by PhpStorm.
 * User: Edward
 * Date: 2019/5/31
 * Time: 13:23
 */
namespace app\admin\controller\warehouse;

use app\admin\BaseController;
use app\admin\controller\account\Group;
use app\admin\model\Admin;
use app\admin\model\Product;
use app\admin\model\ProductCategoryRelation;
use app\admin\model\ProductGroupRelation;
use app\admin\model\ProductPrice;
use app\admin\model\ProductSku;
use app\admin\model\Shop;
use app\admin\model\WarehouseGeneral;
use app\admin\model\WarehouseLog as WarehouseLogModel;
use app\admin\model\WarehouseGeneralSku;
use app\admin\model\WarehouseListDetail;
use app\lib\exception\FailedException;
use app\lib\exception\NotFoundException;
use app\lib\exception\ParameterException;
use app\Validate\IdMustPositive;
use app\Validate\PageMustValidate;
use app\Validate\WarehouseAddValidate;
use think\Request;
use think\Db;

class Warehouse extends BaseController {
    /**
     * 添加其他仓库
     * @param Request $request
     * @return \think\response\Json
     * @throws FailedException
     * @throws ParameterException
     */
    public function warehouseAdd(Request $request){
        (new WarehouseAddValidate())->goCheck();
         $post = $request->post();
         $count = (new Admin())->getCount([['id','eq',$post['warehouse_admin_id']]]);
         if($count==0){
             throw new FailedException(['msg'=>'管理员账号不存在']);
         }
         $new = [
             'type'=>4,
             'warehouse_name'=>$post['warehouse_name'],
             'warehouse_admin_id'=>$post['warehouse_admin_id']
         ];
         $res = (new \app\admin\model\Warehouse())->dataUpdate($new);
         if(!$res){
             throw new FailedException(['msg'=>'操作失败']);
         }else{
             return writeJson(200);
         }
    }

    /**
     * 编辑其他仓库
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws FailedException
     */
    public function warehouseEdit(Request $request){
        (new WarehouseAddValidate())->goCheck();
        (new IdMustPositive())->goCheck();
        $post = $request->post();
//        $count = (new Admin())->getCount([['id','eq',$post['warehouse_admin_id']]]);
//        if($count==0){
//            throw new FailedException(['msg'=>'管理员账号不存在']);
//        }

        $new = [
            'id' =>$post['id'],
//            'type'=>4,
            'warehouse_name'=>$post['warehouse_name'],
            'warehouse_admin_id'=>isset($post['warehouse_admin_id']) ? $post['warehouse_admin_id'] : 1
        ];
        $res = (new \app\admin\model\Warehouse())->dataUpdate($new);
        if(!$res){
            throw new FailedException(['msg'=>'操作失败']);
        }else{
            return writeJson(200);
        }
    }

    /**
     * 通过仓库id 查询仓库下的产品列表  这个是查询总仓库的方法
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws \think\exception\DbException
     */
    public function warehouse_product_general(Request $request){
        return $this->warehouse_product($request);
    }

    /**
     * 中转仓
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws \think\exception\DbException
     */
    public function warehouse_product_transfer(Request $request){
        return $this->warehouse_product($request);
    }

    /**
     * 门店仓
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws \think\exception\DbException
     */
    public function warehouse_product_shop(Request $request){
        (new PageMustValidate())->goCheck();
        $post = $request->post();
        $where=[];
        if($post['warehouse_id'] == -1){
            $warehouse_id_list = (new \app\admin\model\Warehouse())->where("type",'3')->column("id");
            $where[] = ['a.warehouse_id','in',$warehouse_id_list];
        }
        else{
            $where[] = ['a.warehouse_id','eq',$post['warehouse_id']];
        }
        $where = addWhere($post,['international_code'=>""],$where,2,"b.");//国际编码
        $where = addWhere($post,['name'=>""],$where,2,"c.");//商家名称
        if(isset($post['min_time'])&&isset($post['max_time'])&&$post['min_time']!=""&&$post['max_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','between',[$post['min_time'],$post['max_time']]];
        }elseif (isset($post['min_time'])&&$post['min_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $where[] = ['a.update_time','egt',$post['min_time']];
        }elseif (isset($post['max_time'])&&$post['max_time']!=""){
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','elt',$post['max_time']];
        }
        if(isset($post['category_id'])&&$post['category_id']!=""){
            $product_id = (new ProductCategoryRelation())->where("category_id","eq",$post['category_id'])->column("product_id");
            $where[] = ['a.id','in',$product_id];
        }
        $field = "a.*,b.cover_img,b.product_name,b.sales_actual_number,b.international_code,b.product_code,d.price,a.update_time,c.name as shop_name";
        $dataList = (new WarehouseGeneral())
            ->alias("a")
            ->join("om_product b","a.product_id = b.id","right")
            ->join("om_shop c","c.id = b.uid","right")
            ->join("om_product_price d","a.product_id = d.product_id and d.type = 1 and d.delete_time is null","right")
            ->field($field)
            ->where($where)
            ->order("a.id desc")
            ->paginate($post['page_number'], false, [
                'page' => $post['page']]);
        $dataList=$dataList->toArray();
        $count  = $dataList['total'];
        $dataList = $dataList['data'];
        foreach ($dataList as $key=>$value){
            $dataList[$key]['cover_img'] = imgUrlToAbsolute($value['cover_img']);
            $dataList[$key]['zuhe_code'] = $value['international_code']."/".$value['product_code'];
        }
        $returnList = [
            'count'=>$count,
            'dataList'=>$dataList
        ];
        return writeJson(200,$returnList);
    }

    /**
     * 通过仓库id 查询仓库下的产品列表
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws \think\exception\DbException
     */
    public function warehouse_product(Request $request){
        (new PageMustValidate())->goCheck();
        $post = $request->post();
        if($post['page']==0){
            $post['page'] = 1;
            $post['page_number'] = 9999999;
        }
        $admin = $this->admin;
        $where=[];
        if($post['warehouse_id']==-1){
            $warehouseList = (new \app\admin\model\Warehouse())->where('type',3)->column("id");
            $where[] = ['a.warehouse_id','in',$warehouseList];
        }else{
            $where[] = ['a.warehouse_id','eq',$post['warehouse_id']];
        }

        if($admin['account_type']==2){//如果账号类型是2 就要查商家下的商品
            $where[] =['a.uid','eq',$admin['uid']];
        }
        $where = addWhere($post,['international_code'=>""],$where,2,"b.");//国际编码
        $where = addWhere($post,['name'=>""],$where,2,"c.");//商家名称
        if(isset($post['min_time'])&&isset($post['max_time'])&&$post['min_time']!=""&&$post['max_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','between',[$post['min_time'],$post['max_time']]];
        }elseif (isset($post['min_time'])&&$post['min_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $where[] = ['a.update_time','egt',$post['min_time']];
        }elseif (isset($post['max_time'])&&$post['max_time']!=""){
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','elt',$post['max_time']];
        }
        if(isset($post['group_id'])&&$post['group_id']!=""){  //分组的id
            $product_id = (new ProductGroupRelation())->where("group_id","eq",$post['group_id'])->column("product_id");
            $where[] = ['b.id','in',$product_id];
        }
        if(isset($post['category_id'])&&$post['category_id']!=""){  //三级分类的id
            $product_id = (new ProductCategoryRelation())->where("category_id","eq",$post['category_id'])->column("product_id");
            $where[] = ['a.product_id','in',$product_id];
        }
        $field = "a.*,b.cover_img,b.product_name,b.sales_actual_number,b.international_code,b.product_code,c.name as shop_name,d.price";
        $dataList = (new WarehouseGeneral())
            ->alias("a")
            ->join("om_product b","a.product_id = b.id","right")
            ->join("om_shop c","c.id = b.uid","left")
            ->join("om_product_price d","a.product_id = d.product_id and d.type = 1 and d.delete_time is null","left")
            ->field($field)
            ->where($where)
            ->whereNull("b.delete_time")
            ->order("a.id desc")
            ->paginate($post['page_number'], false, [
                'page' => $post['page']]);

        $dataList=$dataList->toArray();
        $count  = $dataList['total'];
        $dataList = $dataList['data'];
        foreach ($dataList as $key=>$value){
            $dataList[$key]['cover_img'] = imgUrlToAbsolute($value['cover_img']);
        }
        $returnList = [
            'count'=>$count,
            'dataList'=>$dataList
        ];
        return writeJson(200,$returnList);
    }

    /**
     * 通过仓库id 查询仓库下的预警管理
     * @param Request $request
     * @return \think\response\Json
     * @throws \app\lib\exception\ParameterException
     * @throws \think\exception\DbException
     */
    public function warehouseList(Request $request){
        (new PageMustValidate())->goCheck();
        $post = $request->post();
        $where=[];
        if(empty($post['remark_id'])){
            $where[] = ['a.warn_stock','egt',Db::raw("a.`stock`")];
        }else{
            if($post['remark_id'] == 1){
                $where[] = ['a.warn_stock','egt',Db::raw("a.`stock`")];
            }else{
                $where[] = ['a.warn_time','elt',time()];
            }
        }
        $admin = $this->admin;
        if($admin['account_type']==2){//如果账号类型是2 就要查商家下的商品
            $where[] =['a.uid','eq',$admin['uid']];
        }
        $where[] = ['a.warehouse_id','eq',$post['warehouse_id']];
        $where = addWhere($post,['international_code'=>""],$where,2,"b.");//国际编码
        $where = addWhere($post,['name'=>""],$where,2,"c.");//商家名称
        if(isset($post['min_time'])&&isset($post['max_time'])&&$post['min_time']!=""&&$post['max_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','between',[$post['min_time'],$post['max_time']]];
        }elseif (isset($post['min_time'])&&$post['min_time']!=""){
            $post['min_time'] =strtotime($post['min_time']);
            $where[] = ['a.update_time','egt',$post['min_time']];
        }elseif (isset($post['max_time'])&&$post['max_time']!=""){
            $post['max_time'] =strtotime($post['max_time']);
            $where[] = ['a.update_time','elt',$post['max_time']];
        }
        if(!empty($post['category_id'])){
            $product_id = (new ProductCategoryRelation())->where("category_id","eq",$post['category_id'])->column("product_id");
            $where[] = ['a.id','in',$product_id];
        }
        $product_price_model = new ProductPrice();
        $product_sku_model = new ProductSku();

        $field = "a.*,b.cover_img,b.product_name,b.international_code,c.name shop_name";
        $dataList = (new WarehouseGeneralSku())
            ->alias("a")
            ->join("om_product b","a.product_id = b.id","left")
            ->join("om_shop c","c.id = b.uid","left")
            ->field($field)
            ->where($where)
            ->order("a.id desc")
            ->paginate($post['page_number'], false, ['page' => $post['page']]);

        $dataList = $dataList->toArray();

        $count    = $dataList['total'];
        $dataList = $dataList['data'];
        foreach ($dataList as $key=>$value){
            if($value['warn_stock']>=$value['stock']){
                $dataList[$key]['remark_id'] = 1;
            }else{
                $dataList[$key]['remark_id'] = 2;
            }
            $dataList[$key]['sku_name'] = $product_sku_model->where([['sku','=',$value['sku']],['product_id','=',$value['product_id']]])->value('sku_name');
            $dataList[$key]['price'] = $product_price_model->where([['product_id','=',$value['product_id']],['type','=',1]])->value('price');
            $dataList[$key]['warning_strtotime'] = $value['warn_time'];
            $dataList[$key]['warning_time'] = date('Y-m-d',$value['warn_time']);
            $dataList[$key]['cover_img'] = imgUrlToAbsolute($value['cover_img']);
        }
        $returnList = [
            'count'=>$count,
            'dataList'=>$dataList,
        ];
        return writeJson(200,$returnList);
    }

    /**
     * 清仓
     * @param Request $request
     * @throws FailedException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function cleanWarehouse(Request $request){
        $post = $request->post();
        //获取批次ID  根据其获取仓库ID 商品ID
        if(!isset($post["id"]) || $post["id"] <= 0){
            throw (new FailedException(['msg'=>'当前ID有误']));
        }
        //获取批次信息
        $skuModel = (new WarehouseGeneralSku())
            ->field("id,uid,product_id,stock,warehouse_id,warn_stock")
            ->where(["id"=>$post["id"]])->find();

        Db::startTrans();
        $skuModel["delete_time"] = time();
        $res = (new \app\admin\model\WarehouseGeneralSku())->fieldUpdate("delete_time",time(),"",["id"=>$post["id"]]);
        if(!$res){
            Db::rollback();
            throw (new FailedException(['msg'=>'清理批次失败']));
        }

        if($skuModel["stock"] == 0){
            //当前批次为0 时不进行仓库数量改变
            Db::commit();
            return writeJson(200,[],'操作成功');
        }

        $where[] = ["product_id","eq",$skuModel["product_id"]];
        $where[] = ["warehouse_id","eq",$skuModel["warehouse_id"]];
        $warModel = (new \app\admin\model\WarehouseGeneral())->where($where)->find();
        if($warModel["stock"] - $skuModel["stock"] < 0){
            Db::rollback();
            throw (new FailedException(['msg'=>'修改库存失败,当前库存不足']));
        }
        //更新仓库库存
        $res = (new \app\admin\model\WarehouseGeneral())->fieldUpdate("stock",$warModel["stock"] - $skuModel["stock"],"",$where);
        if(!$res){
            Db::rollback();
            throw (new FailedException(['msg'=>'修改库存失败']));
        }

        Db::commit();
        return writeJson(200,[],'操作成功');
    }

    /**
     * 库存详情
     * @param Request $request
     * @return \think\response\Json
     * @throws NotFoundException
     * @throws \app\lib\exception\ParameterException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function warehouseGeneralSkuDetail(Request $request){
        (new IdMustPositive())->goCheck();
        $post = $request->post();
        $field = "a.*,b.cover_img,ifNull(b.visitor_number,0) as visitor_number,
        ifNull(b.hot_number,0) as hot_number,b.product_name,ifNull(b.sales_actual_number,0) as sales_actual_number,
        b.international_code,b.product_code,d.price";
        $where[] = ['a.id','eq',$post['id']];
        //仓库单条商品的详情
        $detail = (new WarehouseGeneral())
            ->alias("a")
            ->join("om_product b","a.product_id = b.id","right")
            ->join("om_shop c","c.id = a.uid","left")
            ->join("om_product_price d","a.product_id = d.product_id and d.type = 1 and d.delete_time is null","left")
            ->field($field)
            ->where($where)
            ->find();
        if(!$detail){
            throw new NotFoundException(['msg'=>'记录不存在']);
        }
        $detail['cover_img'] = imgUrlToAbsolute($detail['cover_img']);

        //批次信息
        $where_detail = [['a.product_id','eq',$detail['product_id']], ['b.status','eq',2], ['b.type','in',[1,2]]];
        //如果有仓库信息，则需要进行筛选
        if(isset($post["warehouse_id"])){ $where_detail[] = ['d.warehouse_id','eq',$post["warehouse_id"]]; }

        $field = "a.*,b.type,d.stock,d.id as sku_id,b.order_number";
        $approval = (new WarehouseListDetail())
            ->alias('a')
            ->join("om_warehouse_list b","a.list_id = b.id","right")
            ->join("om_warehouse_general_sku d","d.list_detail_id = a.id")
            ->order("list_id desc")
            ->where($where_detail)
            ->field($field)
            ->select();

        $batch =[];
        foreach ($approval as $key=>$value){
            $batch[$value['list_id']]['create_time'] = $value['create_time'];
            $batch[$value['list_id']]['batch_number'] = $value['order_number'];
            $batch[$value['list_id']]['batchList'][] = [
                'id'=>$value['sku_id'],
                'sku'=>$value['sku'],
                'plan_stock'=>$value['plan_stock'],
                'actual_stock' =>$value['actual_stock'],
                'stock' =>$value['stock'],
                'warning_time'=>date("Y-m-d H:i",$value['warning_time']),
                'warning_stock'=>$value['warning_stock'],
                'warning_status'=>empty($value['remark_id'])?1:2//1是正常 2是预警
            ];
        }
        $batch = array_values($batch);
        $return_data = [
          'batch'=>$batch,
          'detail'=>$detail
        ];
        return writeJson(200,$return_data);

    }

    /**
     * 通过仓库筛选商品
     * international_code 国际编码
     * @param Request $request
     * @return \think\response\Json
     * @throws \think\exception\DbException
     * @throws ParameterException
     */
    public function searchWarehouseProduct(Request $request){
        (new PageMustValidate())->goCheck();
        $post = $request->post();
        if(!isset($post['warehouse_id'])){
            throw new ParameterException(['msg'=>'请选择仓库']);
        }
        $where =[];
        $where[] =['a.warehouse_id','eq',$post['warehouse_id']];
        $where = addWhere($post,['international_code'=>'','product_name'=>''],$where,1,"b.");//国际编码 和商品名称
        $where = addWhere($post,['name'=>''],$where,1,"e.");//搜索商家名称
        $field = "c.sku_code,c.sku,c.sku_name,b.id,b.uid,b.cover_img,b.product_name,b.international_code,a.stock warehouse_stock,d.price,e.name shop_name,a.warn_stock";
        $product = (new \app\admin\model\WarehouseGeneralSku())
            ->alias("a")
            ->join("om_product b","a.product_id = b.id","left")
            ->join("om_product_sku c","a.product_id = c.product_id and a.sku = c.sku","left")
            ->join("om_shop e","e.id = b.uid","left")
            ->join("om_product_price d","a.product_id = d.product_id and d.type = 1 and d.delete_time is null","left")
            ->field($field)
            ->where($where)
            ->order("a.id desc")
            ->paginate($post['page_number'], false, [
                'page' => $post['page']])->each(function ($item){
                $item['cover_img'] = imgUrlToAbsolute($item['cover_img']);
                return $item;
            });
        $product = $product->toArray();
        return writeJson(200,['data'=>$product['data'],'count'=>$product['total']]);
    }

    /**
     * 查询 仓库列表  通过仓库类型 1 是总仓库 2是中转仓库 3是门店仓库  目前总仓库就一个 中转仓就一个 一个商家一个门店仓 审核成功建立一个门店仓4 是其他仓库
     * @param Request $request
     * @return \think\response\Json
     * @throws ParameterException
     */
    public function warehouseTypeList(Request $request){
        (new PageMustValidate())->goCheck();
        $post = $request->post();
        if(!isset($post['type'])||!is_array($post['type'])){
            throw  new ParameterException(['msg'=>'请选择仓库类型']);
        }
        $where = [];
        $where[] =['type','in',$post['type']];//数组类型
        $where['page'] = $post['page'];
        $dataList = (new \app\admin\model\Warehouse())->wechatListTotalWith("admin",$where,"*","create_time desc",$post['page_number']);
        foreach ($dataList['data'] as &$value){
            if($value['type'] == 3){
                $value['shop_name'] = (new Shop())->findOne([['id','eq',$value['uid']]],"name");
            }
            $value['product_number'] = (new WarehouseGeneral())->where([['warehouse_id','eq',$value['id']]])->group("product_id")->count();
        }
        return writeJson(200,$dataList);
    }

    /**
     * 导出商品库存模板
     * 传入uid 则导出商家所有商品批次
     * 传入id  则导出单一商品批次
     * id的优先级大于uid，同时出现则查询ID 数据
     * @param Request $request
     * @return void
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function exportWarehouseExcel(Request $request){
        $info = $request->get();
        if(!isset($info["id"]) && !isset($info["uid"])){
            return ajax_return('500','请选择商品或商家','500');
        }

//        获取总仓库的库存
//        $where["warehouse_id"] = 1;
        if(isset($info["id"])) { $where["id"] = $info["id"]; }
        else if(isset($info["uid"])){ $where["uid"] = $info["uid"]; }

        //【SQL】
        //select
        //om_warehouse_general_sku.id,
        //om_warehouse_general_sku.product_id,
        //product_name,
        //international_code,
        //om_product.sku_remark,
        //om_warehouse_general_sku.stock,
        //om_warehouse_general_sku.create_time,
        //warn_stock,
        //warn_time,
        //product_time
        //from om_warehouse_general_sku
        //inner join om_product on om_warehouse_general_sku.product_id = om_product.id
        //where warehouse_id = 1
        //--and om_warehouse_general_sku.product_id = 2213
        //--and uid = 213
        //获取商品
//        $warehouseGeneralSkuService = new WarehouseGeneralSku();
//        $list = $warehouseGeneralSkuService
//            ->field("om_warehouse_general_sku.id,om_warehouse_general_sku.product_id,product_name,international_code,om_product.sku_remark,om_warehouse_general_sku.stock,om_warehouse_general_sku.product_time,warn_stock,from_unixtime(warn_time, '%Y-%m-%d') as warn_time")
//            ->where($where)
//            ->join("om_product","om_warehouse_general_sku.product_id = om_product.id")
//            ->order("product_id")
//            ->select()
//            ->toArray();

        //获取商品
        //select id,international_code,product_name,sku_remark  from om_product where uid = 264
        $productModel = new product();
        $list = $productModel
            ->field("id,international_code,product_name,sku_remark")
            ->where($where)
            ->order("id")
            ->select()
            ->toArray();

        $excel = new \PHPExcel();//创建PHPExcel对象
        $excel->getProperties()->setTitle("入库模板");//设置标题
        $excel->setActiveSheetIndex(0);

        //第一行设置内容【说明设置】
        $excel->getActiveSheet()->setCellValue('A1',"[注意事项]<1>修改：本表仅限当前库存可修改 <2>新增：批次ID填写0，将剩余的信息填写完整。");
        //合并
        $excel->getActiveSheet()->mergeCells('A1:I1');
        /*设置sheet的name*/
        $excel->getActiveSheet()->setTitle('入库模板');
        //初始化letter
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T', 'U','V','W','X','Y','Z','AA','AB','AC');
        //设置列标题
        $excel->getActiveSheet()->setCellValue("A2", "批次ID(入库填0)");
        $excel->getActiveSheet()->setCellValue("B2", "商品ID");
        $excel->getActiveSheet()->setCellValue("C2", "商品名称");
        $excel->getActiveSheet()->setCellValue("D2", "国际编码");
        $excel->getActiveSheet()->setCellValue("E2", "规格");
        $excel->getActiveSheet()->setCellValue("F2", "导入库存");
        $excel->getActiveSheet()->setCellValue("G2", "生产日期(2000/01/01)");
        $excel->getActiveSheet()->setCellValue("H2", "预警库存");
        $excel->getActiveSheet()->setCellValue("I2", "预警时间(2000/01/01)");
        //设置单元格内容水平居中
        for ($n=0;$n < 9;$n++) {
            $excel->getActiveSheet()->getStyle($letter[$n]."2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }
        //设置列宽度
        $excel->getActiveSheet()->getColumnDimension("D")->setWidth(20);
        $excel->getActiveSheet()->getColumnDimension("G")->setWidth(20);
        $excel->getActiveSheet()->getColumnDimension("I")->setWidth(20);

        //写入数据
        $count = count($list);
        for ($i=0;$i<$count;$i++) {
            //先确定行 ，表头占用一行，所以加3
            $row = $i+3;//再确定列
            $model = $list[$i];
//            $model = array_values($model);
//
//            dumo($model);

            $excel->getActiveSheet()->setCellValue($letter[0].$row, 0);
            //设置单元格内容水平居中
            $excel->getActiveSheet()->getStyle($letter[0].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $excel->getActiveSheet()->setCellValue($letter[1].$row, $model["id"]);
            $excel->getActiveSheet()->getStyle($letter[1].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $excel->getActiveSheet()->setCellValue($letter[2].$row, $model["product_name"]);
            $excel->getActiveSheet()->getStyle($letter[2].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $excel->getActiveSheet()->setCellValue($letter[3].$row, $model["international_code"]);
            $excel->getActiveSheet()->getStyle($letter[3].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $excel->getActiveSheet()->setCellValue($letter[4].$row, $model["sku_remark"]);
            $excel->getActiveSheet()->getStyle($letter[4].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//            for ($j = 0; $j< count($model); $j++) {
//            $excel->getActiveSheet()->setCellValue($letter[$j].$row, $model[$j]);
//            //设置单元格内容水平居中
//            $excel->getActiveSheet()->getStyle($letter[$j].$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//            }
        }
        //生成2007excel格式的xlsx文件-并下载
        header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition:attachment;filename="商品入库模板导出' . date('Y-m-d') .  '.xlsx"');
        header('Cache-Control:max-age=0');
        $objWriter =\PHPExcel_IOFactory:: createWriter($excel, 'Excel2007');
        $objWriter->save( 'php://output');
        exit;
    }

    /**
     * 导入库存模板
     * @param Request $request
     * @return void
     * @throws FailedException
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function addWarehouseExcel(Request $request){
        $post = $request->post();
        if(!isset($post["warehouse_id"])){
            return ajax_return('500','请选择仓库','500');
        }
        $admin = $this->admin;
        $file = request()->file('file');
        if ($file) {
            $info = $file->move('./uploads/exam');
            $path = '/home/wwwroot/api/public/uploads/exam/' . $info->getSaveName();   //服务器环境
//            $path = 'D:/phpStudy/PHPTutorial/WWW/oumaos/public/uploads/exam/' . $info->getSaveName();
//            $path = 'D:/Web/oumaos/public/uploads/exam/' . $info->getSaveName();
//            $path = 'H:/phpProject/oumao/public/uploads/exam/' . $info->getSaveName();
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');//use excel2007 for 2007 format
            $objPHPExcel = $objReader->load($path,$encode = 'utf-8');//$file_url即Excel文件的路径
            $data = $objPHPExcel->getSheet(0)->toArray();//转换为数组格式

            $warehouseGeneralService = new WarehouseGeneral();
            $warehouseGeneralSkuService = new WarehouseGeneralSku();
            $warehouseLogService = new WarehouseLogModel();
            $ProductService = new Product();
            //提示信息：
            $str = "";
            $newList = [];
            try{
                //开始导入数据
                Db::startTrans();
                $newAll = [];  //记录涉及的仓库+商品
                foreach($data as $k=>$v){
                    if($k<2){ continue; } //跳过前两行说明
                    if($v[0] === null){ continue; } //跳过空行
                    //获取数据判断是修改还是新增
                    if($v[0] == 0){ //新增
                        //组成新的数组
                        array_push($newList,$v);
                    }elseif ($v[0] > 0){ //修改
                        //根据id获取批次信息，并修改该批次的数据
                        $where["id"] = $v[0];
                        $model = $warehouseGeneralSkuService->findOne($where);
                        if($v[5] == $model["stock"]){ continue; }
                        $number = $v[5] > $model["stock"] ? $v[5] - $model["stock"] : $model["stock"] - $v[5];
                        $warehouseLogService->insertLog($admin["id"],$model["product_id"],"[将商品批次".$v[0]."的数量修改为".$number."]",$model["warehouse_id"]);
                        if(!$warehouseGeneralSkuService->fieldUpdate("stock",$v[5],[],$where)){
                            $str .= "该商品批次ID下更新失败，请注意查看:批次ID：".$v[0]."-商品id:";
                        }
                        //记录涉及的仓库+商品
                        $newAll[] = array("product_id"=>$model["product_id"],"warehouse_id"=>$model["warehouse_id"]);
                    }else{
                        continue;
                    }
                }

                //重新计算总仓库改变的库存
                foreach ($newAll as $k=>$v){
                    //查询当前剩余的总库存
                    $stock = $warehouseGeneralSkuService->where($v)->sum("stock");
                    //更新总仓库
                    $res = $warehouseGeneralService->fieldUpdate("stock",$stock,"",$v);
                    if(!$res){
                        Db::rollback();
                        throw (new FailedException(['msg'=>'更新总仓库失败']));
                    }
                }

                //有非空行
                if(count($newList) != 0){
                    //创建入库单
                    $new = [
                        'type'=>1,
                        'uid'=>$this->admin["uid"],
                        'from_warehouse_id'=>0,
                        'warehouse_id'=>$post["warehouse_id"],
                        'total_stock'=>0,
                        'order_number'=>(new \app\admin\model\WarehouseList())->createOrderNumber("WL"),
                        'edit_status' =>1,
                        'status'=>1
                    ];

                    //获取总入库数
                    foreach($newList as $key=>$value){
                        $new['total_stock'] += $value[5]?$value[5]:0;
                    }

                    $list_id = (new \app\admin\model\WarehouseList())->dataUpdate($new);
                    if(!$list_id){
                        Db::rollback();
                        throw (new FailedException(['msg'=>'添加总订单失败']));
                    }
                    $newModelList = [];
                    foreach ($newList as $key=>$value){
                        //只有商品id
                        //根据商品ID获取商家
                        $where["id"] = $value[1];
                        $model = $ProductService->findOne($where);
                        $newModelList[] =[
                            'uid'    =>$model['uid'],
                            'list_id'=>$list_id,
                            'sku'=>$value[4],
                            'product_id'=>$value[1],
                            'plan_stock'=> isset($value[5])?$value[5]:0,//计划入库
                            'actual_stock'=>isset($value[5])?$value[5]:0,//实际入库
                            'product_time'=>isset($value[6])?strtotime($value[6]):time(),//生产时间
                            'warning_time'=>isset($value[8])?strtotime($value[8]):time(),//预警时间
                            'warning_stock'=>isset($value[7])?$value[7]:0,//预警库存
                            'create_time'  =>time(),
                            'update_time'  =>time(),
                        ];
                    }
                    if(count($newModelList)==0){
                        if(!$list_id){
                            Db::rollback();
                            throw (new FailedException(['msg'=>'请先添加商品']));
                        }
                    }
                    $res = (new WarehouseListDetail())->saveAll($newModelList);
                    if(!$res){
                        Db::rollback();
                        throw (new FailedException(['msg'=>'生成订单失败']));
                    }
                }

                //结束导入数据
                Db::commit();
                return ajax_return('1','导入成功');
            }catch (Exception $exception){
                Db::rollback();
                throw new FailedException([
                    'msg' => $exception->getMessage()
                ]);
            }
        }
    }
}